/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.dao;

import com.aptech.utility.DBType;
import com.aptech.utility.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Mr.TeTua
 */
public class SaleInvoiceManager {

    public static ResultSet displayAllRows() throws SQLException {
        String SALEINVOICE_FILTER_SALEID = "SELECT Customers.CustomerID"
                + ", Customers.CustomerName"
                + ", SalesInvoice.SalesInvoiceID"
                + ", Drugs.DrugName"
                + ", SalesInvoiceDetails.UnitPrice"
                + ", SalesInvoiceDetails.Quantity"
                + ", SalesInvoiceDetails.Discount"
                + ", SalesInvoice.SubTotal"
                + ", SalesInvoice.ModeOfPayment"
                + ", SalesInvoice.Date"
                + " FROM SalesInvoice"
                + " INNER JOIN Customers"
                + " ON SalesInvoice.CustomerID = Customers.CustomerID"
                + " INNER JOIN SalesInvoiceDetails"
                + " ON SalesInvoice.SalesInvoiceID = SalesInvoiceDetails.SalesInvoiceID"
                + " INNER JOIN Drugs"
                + " ON SalesInvoiceDetails.DrugID = Drugs.DrugID";
        Connection conn = DBUtil.getConnection(DBType.myConnection);

        PreparedStatement preparedStatement = conn.prepareStatement(SALEINVOICE_FILTER_SALEID,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    public static boolean update(String mode, int id) throws SQLException {

        String sql_Update = "UPDATE SalesInvoice"
                + " SET ModeOfPayment = ?"
                + " WHERE SalesInvoice.SalesInvoiceID=?";

        Connection conn = DBUtil.getConnection(DBType.myConnection);

        int affected = 0;

        PreparedStatement pstm_Update = conn.prepareStatement(sql_Update,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

        pstm_Update.setString(1, mode);
        pstm_Update.setInt(2, id);
        affected = pstm_Update.executeUpdate();

        return affected == 1;
    }

}
